package com.allwees.bs.c.module.sys.repository;

import com.allwees.bs.c.module.base.repository.BaseRepository;
import com.allwees.bs.c.module.sys.entity.RegionEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 *
 *
 * @author yangylsky
 * @since 2019-09-07
 *
 */
public interface RegionRepository extends BaseRepository<RegionEntity, Integer> {
	@Query("FROM RegionEntity WHERE id=:countryId AND provinceId=0 AND city_id=0")
	RegionEntity findCountry(@Param("countryId") int countryId);

	@Query("FROM RegionEntity WHERE id=:provinceId AND cityId=0")
	RegionEntity findProvince(@Param("provinceId") int provinceId);

	@Query("FROM RegionEntity WHERE id=:cityId AND cityId != 0")
	RegionEntity findCity(@Param("cityId") int cityId);

	@Query("FROM RegionEntity WHERE"
			+ " (regionNameZhHans=:country OR regionNameZhHant=:country OR regionNameEn=:country OR iso3166=:country)"
			+ " AND provinceId=0")
	RegionEntity findCountryByName(@Param("country") String country);

	@Query("FROM RegionEntity WHERE"
			+ " (regionNameZhHans=:province OR regionNameZhHant=:province OR regionNameEn=:province OR iso3166=:province)"
			+ " AND cityId=0")
	RegionEntity findProvinceByName(@Param("province") String province);

	@Query(value = "select * from res_region where city_id != 0 AND (region_name_zh_hans = ?1 OR region_name_zh_hant = ?1 OR region_name_en = ?1 OR iso3166 = ?1) limit 1", nativeQuery = true)
	RegionEntity findCityByName(String city);
}
